package org.jeecg.modules.ws.util;

import cn.hutool.core.date.DateUtil;
import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import jodd.util.StringUtil;
import org.apache.poi.ss.usermodel.*;
import org.jeecg.common.exception.JeecgBootException;
import org.jeecg.common.util.DateUtils;
import org.jeecg.modules.ws.dto.UploadYdbgDto;
import org.jeecg.modules.ws.entity.WsCapitalDetail;
import org.jeecg.modules.ws.entity.WsCapitalInfo;
import org.jeecg.modules.ws.entity.WsCompany;
import org.jeecg.modules.ws.entity.WsYdbg;
import org.jeecg.modules.ws.service.IWsCapitalDetailService;
import org.jeecg.modules.ws.service.IWsCapitalInfoService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.transaction.annotation.Transactional;

import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.util.ArrayList;
import java.util.List;

/**
 * 资金账表导入
 *
 * @author ZJJ
 * @date 2025-07-25 下午2:50
 * @copyright Copyright (c) 2025 ZJJ
 */
@Component
public class CapitalExcelUtil {
    @Autowired
    private IWsCapitalInfoService infoService;
    @Autowired
    private IWsCapitalDetailService detailService;


    /**
     * 导入资金详情情况
     *
     * @param inputStream 输入流
     * @return 返回值
     */
    @Transactional(rollbackFor = Exception.class)
    public List<WsCapitalDetail> importCapitalDetail(InputStream inputStream) throws IOException {
        //5.2、创建工作薄
        Workbook workbook = WorkbookFactory.create(inputStream);
        //5.3、获取工作薄里面sheet的个数
        int sheetNum = workbook.getNumberOfSheets();
        if (sheetNum != 1) {
            throw new JeecgBootException("暂时只支持单excel导入");
        }
        Sheet sheet = workbook.getSheetAt(0);
        //5.4.1、获取sheet中有数据的行数
        int rows = sheet.getPhysicalNumberOfRows();
        List<WsCapitalDetail> list = new ArrayList<>();
        for (int j = 0; j < rows; j++) {
            //5.4.1.1、过滤掉文件的表头（视文件表头情况而定）
            if (j == 1 || j == 0 || j == 2) {
                continue;
            }
            // 5.4.1.2 获取当前行
            Row row = sheet.getRow(j);
            if (row == null) {
                // 如果行为空，跳过
                continue;
            }
            // 初始化变量
            String ysdw = null;
            String xmbm = null;
            String xmmc = null;
            Double xdje = null;
            String zfsj = null;
            String zfrmc = null;
            String zfrzh = null;
            String zfrkhh = null;
            String skrmc = null;
            String skrzh = null;
            String skrkhh = null;
            String yt = null;
            String pzh = null;
            String zbid = null;
            // 获取单元格值并进行非空检查
            Cell cell;
            // 预算单位
            cell = row.getCell(2);
            ysdw = (cell != null && cell.getCellType() == CellType.STRING) ? cell.getStringCellValue() : null;
            // 项目编码
            cell = row.getCell(5);
            xmbm = (cell != null && cell.getCellType() == CellType.STRING) ? cell.getStringCellValue() : null;
            // 项目名称
            cell = row.getCell(6);
            xmmc = (cell != null && cell.getCellType() == CellType.STRING) ? cell.getStringCellValue() : null;
            // 下达金额
            cell = row.getCell(21);
            xdje = (cell != null && cell.getCellType() == CellType.NUMERIC) ? cell.getNumericCellValue() : null;
            // 支付时间
            cell = row.getCell(23);
            zfsj = (cell != null && cell.getCellType() == CellType.STRING) ? cell.getStringCellValue() : null;
            // 支付人名称
            cell = row.getCell(25);
            zfrmc = (cell != null && cell.getCellType() == CellType.STRING) ? cell.getStringCellValue() : null;
            // 支付人账号
            cell = row.getCell(26);
            zfrzh = (cell != null && cell.getCellType() == CellType.STRING) ? cell.getStringCellValue() : null;
            // 支付人开户行
            cell = row.getCell(27);
            zfrkhh = (cell != null && cell.getCellType() == CellType.STRING) ? cell.getStringCellValue() : null;
            // 收款人名称
            cell = row.getCell(28);
            skrmc = (cell != null && cell.getCellType() == CellType.STRING) ? cell.getStringCellValue() : null;
            // 收款人账号
            cell = row.getCell(29);
            skrzh = (cell != null && cell.getCellType() == CellType.STRING) ? cell.getStringCellValue() : null;
            // 收款人开户行
            cell = row.getCell(30);
            skrkhh = (cell != null && cell.getCellType() == CellType.STRING) ? cell.getStringCellValue() : null;
            // 用途
            cell = row.getCell(31);
            yt = (cell != null && cell.getCellType() == CellType.STRING) ? cell.getStringCellValue() : null;
            // 凭证号
            cell = row.getCell(34);
            pzh = (cell != null && cell.getCellType() == CellType.STRING) ? cell.getStringCellValue() : null;
            // 指标id
            cell = row.getCell(37);
            zbid = (cell != null && cell.getCellType() == CellType.STRING) ? cell.getStringCellValue() : null;
            // 创建实体对象
            WsCapitalDetail detail = new WsCapitalDetail();
            detail.setBudgetUnit(ysdw);
            detail.setProjectCode(xmbm);
            detail.setProjectName(xmmc);
            detail.setXdje(xdje == null ? null : new BigDecimal(xdje));
            detail.setPayTime(StringUtil.isNotBlank(zfsj)? DateUtil.parse(zfsj) : null);
            detail.setPayBy(zfrmc);
            detail.setPayNum(zfrzh);
            detail.setPayBank(zfrkhh);
            detail.setPayee(skrmc);
            detail.setPayeeNum(skrzh);
            detail.setPayeeBank(skrkhh);
            detail.setPurpose(yt);
            detail.setProofNum(pzh);
            detail.setIndexId(zbid);
            list.add(detail);
        }
        detailService.saveBatch(list);
        return list;
    }

    /**
     * 导入资金总体情况
     *
     * @param inputStream 输入流
     * @return 返回值
     */
    @Transactional(rollbackFor = Exception.class)
    public List<WsCapitalInfo> importCapitalInfo(InputStream inputStream) throws IOException {
        //5.2、创建工作薄
        Workbook workbook = WorkbookFactory.create(inputStream);
        //5.3、获取工作薄里面sheet的个数
        int sheetNum = workbook.getNumberOfSheets();
        if (sheetNum != 1) {
            throw new JeecgBootException("暂时只支持单excel导入");
        }
        Sheet sheet = workbook.getSheetAt(0);
        //5.4.1、获取sheet中有数据的行数
        int rows = sheet.getPhysicalNumberOfRows();
        List<WsCapitalInfo> list = new ArrayList<>();
        for (int j = 0; j < rows; j++) {
            //5.4.1.1、过滤掉文件的表头（视文件表头情况而定）
            if (j == 1 || j == 0 || j == 2 || j == 3) {
                continue;
            }
            // 5.4.1.2 获取当前行
            Row row = sheet.getRow(j);
            if (row == null) {
                // 如果行为空，跳过
                continue;
            }
            // 初始化变量
            String ysdw = null;
            String xmbm = null;
            String xmmc = null;
            Double xdje = null;
            Double jyje = null;
            Double zfhj = null;
            String zfjd = null;
            String zbzy = null;
            // 获取单元格值并进行非空检查
            Cell cell;
            // 预算单位
            cell = row.getCell(1);
            ysdw = (cell != null && cell.getCellType() == CellType.STRING) ? cell.getStringCellValue() : null;
            // 项目编码
            cell = row.getCell(4);
            xmbm = (cell != null && cell.getCellType() == CellType.STRING) ? cell.getStringCellValue() : null;
            // 项目名称
            cell = row.getCell(2);
            xmmc = (cell != null && cell.getCellType() == CellType.STRING) ? cell.getStringCellValue() : null;
            // 下达金额
            cell = row.getCell(32);
            xdje = (cell != null && cell.getCellType() == CellType.NUMERIC) ? cell.getNumericCellValue() : null;
            // 结余金额
            cell = row.getCell(33);
            jyje = (cell != null && cell.getCellType() == CellType.NUMERIC) ? cell.getNumericCellValue() : null;
            // 支付合计
            cell = row.getCell(34);
            zfhj = (cell != null && cell.getCellType() == CellType.NUMERIC) ? cell.getNumericCellValue() : null;
            // 支付进度
            cell = row.getCell(39);
            zfjd = (cell != null && cell.getCellType() == CellType.STRING) ? cell.getStringCellValue() : null;
            // 指标摘要
            cell = row.getCell(42);
            zbzy = (cell != null && cell.getCellType() == CellType.STRING) ? cell.getStringCellValue() : null;
            // 创建实体对象
            WsCapitalInfo info = new WsCapitalInfo();
            String[] split = xmmc.split("-");
            info.setBudgetUnit(ysdw);
            info.setProjectCode(xmbm);
            info.setProjectName(split[0]);
            info.setXdje(xdje == null ? null : new BigDecimal(xdje));
            info.setJyje(jyje == null ? null : new BigDecimal(jyje));
            info.setZfhj(zfhj == null ? null : new BigDecimal(zfhj));
            if (zfjd != null && zfjd.contains("%")) {
                zfjd = zfjd.replace("%", "");
                info.setZcjd(new BigDecimal(zfjd).divide(new BigDecimal(100), 2, RoundingMode.HALF_UP));
            }
            info.setZbzy(zbzy);
            list.add(info);
        }
        infoService.saveBatch(list);
        return list;
    }
}
